* Table 0 - Basic descriptive statistics

qui putexcel set "./tables/tables.xlsx", sheet("Table0") modify

use "./data/publications.dta", clear
createvarspub
drop if year>2019
qui putexcel A1=("Statistics for the publications dataset")
qui putexcel B2=("1970-2019")
qui putexcel C2=("2000-2019")

* # of journals
unique journal
local val1=r(unique)
unique journal if year>1999 
local val2=r(unique)
qui putexcel A3=("# of journals")
qui putexcel B3=("`val1'")
qui putexcel C3=("`val2'")

* # of authors
unique person_id
local val1=r(unique)
unique person_id if year>1999
local val2=r(unique)
qui putexcel A5=("# of auth")
qui putexcel B5=("`val1'")
qui putexcel C5=("`val2'")

* # portuguese authors
unique person_id if portnat==1
local val1=r(unique)
unique person_id if portnat==1 & year>1999
local val2=r(unique)
qui putexcel A6=("# of Port auth")
qui putexcel B6=("`val1'")
qui putexcel C6=("`val2'")

* # port authors with (at least) one port aff
unique person_id if portnat==1 & national==1
local val1=r(unique)
unique person_id if portnat==1 & national==1 & year>1999
local val2=r(unique)
qui putexcel A7=("# Port auth with Port aff")
qui putexcel B7=("`val1'")
qui putexcel C7=("`val2'")

* # port authors with (at least) one foreign aff
unique person_id if portnat==1 & foreign==1
local val1=r(unique)
unique person_id if portnat==1 & foreign==1 & year>1999
local val2=r(unique)
qui putexcel A8=("# Port auth with non port aff")
qui putexcel B8=("`val1'")
qui putexcel C8=("`val2'")

* 
bys person_id: gen totart=_N
gen natper=(national==1)&(portnat==1)
gen forper=(foreign==1)&(portnat==1)
gen natonlyper=(national==1)&(foreign==0)&(portnat==1)
gen foronlyper=(national==0)&(foreign==1)&(portnat==1)
bys person_id: egen tnatper=total(natper)
bys person_id: egen tforper=total(forper)
bys person_id: egen tnatonlyper=total(natonlyper)
bys person_id: egen tforonlyper=total(foronlyper)

* # port auth alwa port affs
unique person_id if totart==tnatper
local val1=r(unique)
unique person_id if totart==tnatper &year>1999 
local val2=r(unique)
qui putexcel A9=("# Port auth always port aff")
qui putexcel B9=("`val1'")
qui putexcel C9=("`val2'")

* # port auth always only port affs
unique person_id if totart==tnatonlyper
local val1=r(unique)
unique person_id if totart==tnatonlyper &year>1999 
local val2=r(unique)
qui putexcel A10=("# Port auth always only port aff")
qui putexcel B10=("`val1'")
qui putexcel C10=("`val2'")

* # port auth always for affs
unique person_id if totart==tforper
local val1=r(unique)
unique person_id if totart==tforper&year>1999 
local val2=r(unique)
qui putexcel A11=("# Port auth always for aff")
qui putexcel B11=("`val1'")
qui putexcel C11=("`val2'")

* # port auth always only for affs
unique person_id if totart==tforonlyper
local val1=r(unique)
unique person_id if totart==tforonlyper &year>1999 
local val2=r(unique)
qui putexcel A12=("# Port auth always only for aff")
qui putexcel B12=("`val1'")
qui putexcel C12=("`val2'")

* Foreign authors with portuguese affiliation
unique person_id if portnat==0 & national==1
local val1=r(unique)
unique person_id if portnat==0 & national==1 & year>1999
local val2=r(unique)
qui putexcel A13=("# for auth with Port aff")
qui putexcel B13=("`val1'")
qui putexcel C13=("`val2'")

unique article_id
local val1=r(unique)
unique article_id if year>1999
local val2=r(unique)
qui putexcel A15=("# of articles")
qui putexcel B15=("`val1'")
qui putexcel C15=("`val2'")

unique article_id if tnational==1
local val1=r(unique)
unique article_id if tnational==1 & year>1999
local val2=r(unique)

qui putexcel A16=("# of articles with Port Aff")
qui putexcel B16=("`val1'")
qui putexcel C16=("`val2'")

unique article_id if tportpub==1
local val1=r(unique)
unique article_id if tportpub==1 & year>1999
local val2=r(unique)

qui putexcel A17=("# of articles with pub+ucp+bp")
qui putexcel B17=("`val1'")
qui putexcel C17=("`val2'")

unique article_id if tpfaonly==1
local val1=r(unique)
unique article_id if tpfaonly==1 & year>1999
local val2=r(unique)

qui putexcel A18=("# of articles with Port aut with non port aff")
qui putexcel B18=("`val1'")
qui putexcel C18=("`val2'")


/*
use "${path7}dtafiles/allauthors.dta", clear

qui putexcel E1=("Statistics for WOS")
qui putexcel E3=("Number of authors")
qui putexcel E4=("Number of journals")
qui putexcel E5=("Number of articles")

unique author
local val1=r(unique)
unique journal
local val2=r(unique)
unique title journal
local val3=r(unique)

qui putexcel F3=("`val1'")
qui putexcel F4=("`val2'")
qui putexcel F5=("`val3'")

*/

use "./data/academics", clear
createvarsacad

qui putexcel H1=("Statistics for academics")
qui putexcel H2=("all teaching & research staff")
unique name
local val1=r(unique)
qui putexcel I2=("`val1'")
qui putexcel H3=("all doctorates")
unique name if doctor==1
local val1=r(unique)
qui putexcel I3=("`val1'")
qui putexcel I5=("2001-2009")
* Auxiliar
count if dfpaux==1&year>2001 &year<2010
local val=r(N)
qui putexcel H6=("First time P Aux")
qui putexcel I6=("`val'")
count if dumpaux==1 & year<2010
local val=r(N)
qui putexcel H7=("Assist -> Aux")
qui putexcel I7=("`val'")
count if dpauxih==1 & year<2010
local val=r(N)
qui putexcel H8=("In-house Assist -> Aux")
qui putexcel I8=("`val'")
* Associado
count if dfpass==1 &year>2001&year<2010
local val=r(N)
qui putexcel H9=("First time P Ass")
qui putexcel I9=("`val'")
count if dumpass==1 &year<2010
local val=r(N)
qui putexcel H10=("Aux -> Ass")
qui putexcel I10=("`val'")
count if dpassih==1&year<2010
local val=r(N)
qui putexcel H11=("In-house Aux -> Ass")
qui putexcel I11=("`val'")
* Catedratico
count if dfpcat==1 &year>2001&year<2010
local val=r(N)
qui putexcel H12=("First time P Cat")
qui putexcel I12=("`val'")
count if dumpcat==1 & year<2010
local val=r(N)
qui putexcel H13=("Ass -> Cat")
qui putexcel I13=("`val'")
count if dpcatih==1&year<2010
local val=r(N)
qui putexcel H14=("In-house Ass -> Cat")
qui putexcel I14=("`val'")

qui putexcel J5=("2010-2018")

count if dfpaux==1&year>2009
local val=r(N)
qui putexcel J6=("`val'")
count if dumpaux==1&year>2009
local val=r(N)
qui putexcel J7=("`val'")
count if dpauxih==1&year>2009
local val=r(N)
qui putexcel J8=("`val'")
* Associado
count if dfpass==1&year>2009
local val=r(N)
qui putexcel J9=("`val'")
count if dumpass==1 &year>2009
local val=r(N)
qui putexcel J10=("`val'")
count if dpassih==1&year>2009
local val=r(N)
qui putexcel J11=("`val'")
* Catedratico
count if dfpcat==1 &year>2009
local val=r(N)
qui putexcel J12=("`val'")
count if dumpcat==1 & year>2009
local val=r(N)
qui putexcel J13=("`val'")
count if dpcatih==1 & year>2009
local val=r(N)
qui putexcel J14=("`val'")

* Years since phd
qui putexcel H16=("Yrs since Phd")
qui putexcel H17=("Aux")
qui putexcel H18=("Ass")
qui putexcel H19=("Cat")
gen daux=dumpaux*(year-phdyear)
sum daux if dumpaux==1 & year<2010
local val=r(mean)
qui putexcel I17=(`val'), nformat(##.0)
sum daux if dumpaux==1 & year>2009
local val=r(mean)
qui putexcel J17=(`val'), nformat(##.0)
gen dass=dumpass*(year-phdyear)
sum dass if dumpass==1 & year<2010
local val=r(mean)
qui putexcel I18=(`val'), nformat(##.0)
sum dass if dumpass==1 & year>2009
local val=r(mean)
qui putexcel J18=(`val'), nformat(##.0)
gen dcat=dumpcat*(year-phdyear)
sum dcat if dumpcat==1 & year<2010
local val=r(mean)
qui putexcel I19=(`val'), nformat(##.0)
sum dcat if dumpcat==1 & year>2009
local val=r(mean)
qui putexcel J19=(`val'), nformat(##.0)
drop daux dass dcat
* Average age
qui putexcel H20=("Age at time")
qui putexcel H21=("Aux")
qui putexcel H22=("Ass")
qui putexcel H23=("Cat")
gen daux=dumpaux*(year-byear)
sum daux if dumpaux==1 & year<2010
local val=r(mean)
qui putexcel I21=(`val'), nformat(##.0)
sum daux if dumpaux==1 & year>2009
local val=r(mean)
qui putexcel J21=(`val'), nformat(##.0)
gen dass=dumpass*(year-byear)
sum dass if dumpass==1 & year<2010
local val=r(mean)
qui putexcel I22=(`val'), nformat(##.0)
sum dass if dumpass==1 & year>2009
local val=r(mean)
qui putexcel J22=(`val'), nformat(##.0)
gen dcat=dumpcat*(year-byear)
sum dcat if dumpcat==1 & year<2010
local val=r(mean)
qui putexcel I23=(`val'), nformat(##.0)
sum dcat if dumpcat==1 & year>2009
local val=r(mean)
qui putexcel J23=(`val'), nformat(##.0)

use "./data/academics", clear
createvarsacad
keep if tenure
sort name year
qui putexcel L1=("Statistics for tenuretrack")
qui putexcel M3=("Number of full-time professors")
unique name
local val=r(unique)
qui putexcel L4=("Overall")
qui putexcel M4=("`val'")
unique name if year==2001
local val=r(unique)
qui putexcel L5=("in 2001")
qui putexcel M5=("`val'")
unique name if year==2008
local val=r(unique)
qui putexcel L6=("in 2008")
qui putexcel M6=("`val'")
unique name if year==2009
local val=r(unique)
qui putexcel L7=("in 2009")
qui putexcel M7=("`val'")
unique name if year==2018
local val=r(unique)
qui putexcel L8=("in 2018")
qui putexcel M8=("`val'")
* Females
unique name if sex==1
local val=r(unique)
qui putexcel L10=("Women")
qui putexcel M10=("`val'")
* Foreigners
unique name if nation!=620
local val=r(unique)
qui putexcel L11=("Foreign")
qui putexcel M11=("`val'")
* Portuguese PhDs
unique name if phdctry==620
local val=r(unique)
qui putexcel L12=("PT PhD")
qui putexcel M12=("`val'")

unique name if phdctry==826
local val=r(unique)
qui putexcel L13=("UK PhD")
qui putexcel M13=("`val'")

unique name if phdctry==840
local val=r(unique)
qui putexcel L14=("US PhD")
qui putexcel M14=("`val'")
